<?php

include_once 'DBCon.php';
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Schedule
 *
 * @author user
 */
class Schedule {

    private $traveltime;
    private $fare;
    private $routename;
    private $schedulename;
    private $departuretime;
    private $con;
    private $scheduleid;
    private $date;
    private $originid;
    private $destinationid;
    private $routeid;
    private $selectschedule = 'SELECT sr.sid, shid, stationName, travelTime, fare, 
        routeName, scheduleName, departureTime FROM station st, stationinroute sr,
        route r, schedule s WHERE st.sid = sr.sid AND sr.rid = r.rid AND r.rid = s.rid 
        AND sr.sid = ? AND s.rid = ?  AND CONCAT(?, " ", departureTime) > ADDTIME(NOW(), "04:00:00")
        AND s.status = "ACV";';
    private $selectrouteid = 'SELECT rid FROM route WHERE routeName = ?;';
    private $selectstationid = 'SELECT sid FROM station WHERE stationName = ?;';
    private $selectschedulestat = 'SELECT r.routeName, s.scheduleName, departureTime FROM schedule s, route r WHERE s.rid = r.rid AND r.rid=?';
    private $selecttotalclass = 'SELECT COUNT(shid) FROM schedule s WHERE rid=?;';
    private $selectTotalRoute = 'SELECT COUNT(rid) FROM route;';
    private $scheduleDetailName = 'SELECT scheduleName FROM schedule WHERE rid=? AND shid=?;';
    private $scheduleDetailTime = 'SELECT departureTime FROM schedule WHERE rid=? AND shid=?;';
    private $selectallsch = 'SELECT shid, scheduleName, departureTime,rid FROM schedule WHERE rid=? AND status="ACV";';
    private $selectRouteName = 'SELECT routeName FROM route WHERE rid=?;';
    private $seatleft = 'SELECT s.* FROM (SELECT count(s.seatid) as emptySeat FROM seat s WHERE s.seatid not in (SELECT bs.seatid FROM bookedseat bs WHERE bookedDate =CURDATE() AND shid =? AND status != "CCL")) s;';

    function __construct() {
        $this->con = DBCon::connectDB();
    }

    public function obtainSchedules() {
        $this->routeid = trim($_POST['route']);
        $this->originid = trim($_POST['origin']);
        $this->destinationid = trim($_POST['destin']);
        $this->date = trim($_POST['date']);
        $oritemp = array();
        $destemp = array();
        $origin = null;
        $destin = null;
        $pstmtselectorigin = $this->con->prepare($this->selectschedule);
        $pstmtselectdestin = $this->con->prepare($this->selectschedule);
        $pstmtselectorigin->bind_param('sss', $this->originid, $this->routeid, $this->date);
        $pstmtselectorigin->bind_result($oritemp['originid'], $oritemp['scheduleid'], $oritemp['origin'], $oritemp['travelTime'], $oritemp['fare'], $oritemp['routeName'], $oritemp['scheduleName'], $oritemp['departureTime']);
        $pstmtselectorigin->store_result();
        $pstmtselectorigin->execute();
        while ($pstmtselectorigin->fetch()) {
            foreach ($oritemp as $key => $value) {
                $row[$key] = $value;
            }
            $origin[] = $row;
        }
        $pstmtselectdestin->bind_param('sss', $this->destinationid, $this->routeid, $this->date);
        $pstmtselectdestin->bind_result($destemp['destinid'], $destemp['scheduleid'], $destemp['destin'], $destemp['travelTime'], $destemp['fare'], $destemp['routeName'], $destemp['scheduleName'], $destemp['departureTime']);
        $pstmtselectdestin->store_result();
        $pstmtselectdestin->execute();
        while ($pstmtselectdestin->fetch()) {
            foreach ($destemp as $key => $value) {
                $row[$key] = $value;
            }
            $destin[] = $row;
        }
        if ($origin != null && $destin != null) {
            $schedule = array(
                'origin' => $origin,
                'destin' => $destin
            );
            return $schedule;
        } else {
            return null;
        }
    }

    public function getRouteAndStationInfo($route, $origin, $destin) {
        $data = array();
        $pstmtroute = $this->con->prepare($this->selectrouteid);
        $pstmtroute->bind_param('s', $route);
        $pstmtroute->bind_result($this->routeid);
        $pstmtroute->store_result();
        $pstmtroute->execute();
        $pstmtroute->fetch();
        $data['rid'] = $this->routeid;
        $pstmtroute->close();
        $pstmtorigin = $this->con->prepare($this->selectstationid);
        $pstmtorigin->bind_param('s', $origin);
        $pstmtorigin->bind_result($this->originid);
        $pstmtorigin->store_result();
        $pstmtorigin->execute();
        $pstmtorigin->fetch();
        $data['originid'] = $this->originid;
        $pstmtorigin->close();
        $pstmtdestin = $this->con->prepare($this->selectstationid);
        $pstmtdestin->bind_param('s', $destin);
        $pstmtdestin->store_result();
        $pstmtdestin->bind_result($this->destinationid);
        $pstmtdestin->execute();
        $pstmtdestin->fetch();
        $data['destinid'] = $this->destinationid;
        $pstmtdestin->close();
        return $data;
    }

    public function getRouteName($rid) {
        $pstmt = $this->con->prepare($this->selectRouteName);
        $pstmt->bind_param('s', $rid);
        $pstmt->bind_result($rName);
        $pstmt->execute();
        if ($pstmt->fetch()) {
            return $rName;
        }
    }

    public function getTotalRoute() {
        $pstmt = $this->con->prepare($this->selectTotalRoute);
        $pstmt->bind_result($total);
        $pstmt->execute();
        if ($pstmt->fetch()) {
            $pstmt->close();
            return $total;
        }
    }

    public function getTotalCountSchedule($routeid) {
        $pstmt = $this->con->prepare($this->selecttotalclass);
        $pstmt->bind_param('s', $routeid);
        $pstmt->bind_result($total);
        $pstmt->execute();
        if ($pstmt->fetch()) {
            return $total;
        }
    }

    public function getScheduleName($routeid, $scheduleid) {
        $pstmt = $this->con->prepare($this->scheduleDetailName);
        $pstmt->bind_param('ss', $routeid, $scheduleid);
        $pstmt->bind_result($sName);
        $pstmt->execute();
        if ($pstmt->fetch()) {
            return $sName;
        }
    }

    public function getScheduleTime($routeid, $scheduleid) {
        $pstmt = $this->con->prepare($this->scheduleDetailTime);
        $pstmt->bind_param('ss', $routeid, $scheduleid);
        $pstmt->bind_result($dTime);
        $pstmt->execute();
        if ($pstmt->fetch()) {
            return $dTime;
        }
    }

    public function getSeatLeft($shid) {
        $left = null;
        $conn = DBCon::connectDB();
        $pstmt = $conn->prepare($this->seatleft);
        $pstmt->bind_param('i', $shid);
        $pstmt->bind_result($left);
        $pstmt->store_result();
        $pstmt->execute();
        if ($pstmt->fetch()) {
            return $left;
        }
    }

    public function displayStatus() {
        $content = '';
        for ($i = 1; $i <= 2; $i++) {
            $pstmt = $this->con->prepare($this->selectallsch);
            $content .= '<fieldset>';
            $content .= '<div class="row" >';
            $content .= '<h5>' . $this->getRouteName($i) . '</h1>';
            $content .= '<table border="0" class="large-10 large-offset-1 columns">';
            $content .= '<tr>';
            $content .= '<td><b>Schedule Name</b></td>';
            $content .= '<td><b>Departure Time</b></td>';
            $content .= '<td><b>Seat left</b></td>';
            $content .= '<td><b>Status</b></td>';
            $content .= '</tr>';
            $pstmt->bind_param('s', $i);
            $pstmt->bind_result($id, $name, $time, $rid);
            $pstmt->store_result();
            $pstmt->execute();
            while ($pstmt->fetch()) {
                $sleft = $this->getSeatLeft($id);
                $content .= '<tr>';
                $content .= '<td>' . $name . '</td>';
                $content .= '<td>' . $time . '</td>';
                $content .= '<td>&nbsp;&nbsp;' . $sleft . '</td>';
                if ($sleft >= 168 && $sleft <= 336) {
                    $content .= '<td color="red">Available</td>';
                } else if ($sleft > 0 && $sleft < 168) {
                    $content .= '<td>Fast Selling</td>';
                } else if ($sleft = 0) {
                    $content .= '<td>Sold out</td>';
                }
                $content .= '</tr>';
            }
            $content .= '</table>';
            $content .='</div>';
            $content .='</fieldset><hr />';
        }



        echo $content;
    }

}

?>
